Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.
Index Scan:
Since a scan touches every row in the table, whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.
Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.
Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then the optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.
Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.
In general query optimizer tries to use an Index Seek which means that the optimizer has found a useful index to retrieve recordset. But if it is not able to do so either because there is no index or no useful indexes on the table, then SQL Server has to scan all the records that satisfy the query condition.
Reference : Pinal Dave (https://blog.sqlauthority.com)
107 Comments. Leave new
In case of non clustered index, index key columns and data in table are stored on different pages. Firstly index key columns are scanned and the column key value which meets the criteria in the predicate condition is then referred to obtain data from a table based on row . So full scanning of table does not take place…Please correct me if I am wrong and If this is the case what is the difference in seek and scan.
May I understand that Index Scan and Table Scan are the same thing? If an Index Scan is performed, it means that the index/s are not being used for that particularly query?. Is this correct?
Hi Pinal,
Can i partitioned a table which is already used in Merge replication or Peer to Peer replication as an article.
i.e. Table XYZ is replicated using merge or Peer to Peer replication from server S1 to Server S2. Table is of 700Gb and i want to partitioned the tables.
Please, let me know how to do it. What is the effect of that on publisher and subscriber in merge replication ?
Hi Pinal,
how can we decide that there should be a index scan or index seek?
Hi Pinal,
one of the best solution for the Index scan / Table scan and seek
can any one please provide me one of live example of indexes and with out indexes, Many Thanks
in
select * from MYTABLE
WHERE SyrogatePrimaryKolumn=’X’ and NonClusteredIndexColumn=’Y’ and NoIndexColumn=’Z’
the optimizer will use use SEEK on Primary Clustered Index
but in
select * from MYTABLE
where SyrogatePrimaryKolumn>=1 and SyrogatePrimaryKolumn=1 and SyrogatePrimaryKolumn=’X’ and NonClusteredIndexColumn=’Y’ and NoIndexColumn=’Z’
in this case it will use SEEK of NonClusteredIndex because column of that index has = oprator and KEY LOOKUP on Primary Clustered Index, because in select list is *, not specifed columns of NonClusteredIndex, or INCLUDE list of NonClustered Index.
when column value = then it will use SEEK of index of that column, when it is not = (>=, <=, ,or not specified it will use KEY LOOKUP or SCAN on Primary Clustered Index or SEEK of PrimaryClusteredIndex)
Futher, which index will be used depends what is in SELECT list.
SELECT NonClusteredIndexColumn1, NonClusteredIndexColumn2, NonClusteredIndexColumn3 FROM MYTABLE WHERE NonClusteredIndexColumn1=1
WILL use SEEK of NonClusteredIndex with this 3 Columns
Also it will depend is it ColumnX part of IncludeColumnList of NonClusteredIndex
SELECT NonClusteredIndexColumn2, NonClusteredIndexColumn3, ColumnX FROM MYTABLE WHERE NonClusteredIndexColumn1=1
WILL use SEEK of NonClusteredIndex with this Columns and ColumnX in ICLUDE list of NonClusteredIndex
Litle confusing if you have many indexes, Queries
So I’m thinking only to leftt SurrogatePrimaryKey and replace
Even more if there is NonClustered Index, if I add SyrogatePrimaryKolumn>=1 and SyrogatePrimaryKolumn=1 and SyrogatePrimaryKolumn<9999999999 there is no column with = operator
If you have just ClusteredIndex , there is no INCLUDE list, and it will use SCAN not SEEK of ClusteredIndex, if in WHERE list beside SyrogatePrimaryKolumn>=1 and SyrogatePrimaryKolumn<9999999999 there is no column with = operator
It is wrong conclusion with = operator, it will work with SEEK for any operator , but I make mistake with SyrogatePrimaryKolumn<999999999(9) it should be value in my case for int column 2147483647
Index choice would be dependent on data distribution also.
I don’t know why, but comments I wrote are different after Post Comment, that was the reason that I put several of them, many times. Please be kind to put last 5 in one comment and to delete other ones. There are still some mistakes, but than I will be make corrections
please put the first one of last 5
Hi Pinal,
It’s very useful
Thanks
Paval
Hi pinal,
i think index scan definition above is not always correct. As index/Table scan can touch only some rows and leave rest of the rows, Thanks for the query optimizer team, for example semi join applied on the nested loop join or final Row Goal applied etc.
Interesting. Do you have any sample code?
If a table has 3 columns and every column has an index associated with it. If I query the table with all the columns specified in the ‘Where’ clause. So how many index will SQL server use.
If Index Scan is Table scan then whats the difference between them .
Hi Pinal,
i have one question here, when i plan to set non-clustered indexes for my table.
suppose i have a table called test and has 4 columns like col1, col2, col3,col4. when my application is running, it may send queries to database as below.
1. select * from test where col1=123
2. select * from test where col1=123 and col2=’1
3. select * from test where col1=123 and col2=1 and col3=2
4. select * from test where col1=123 and col2=1 and col3=2 and col4=true
from above, what is best option to create non-clustered index for the above table. should i create separate create indexes for 4 columns or all at once.
what is best method for doing so?
Thanks,
Srinivas